package com.slfx.easy.poi.demo;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.util.PoiMergeCellUtil;
import cn.hutool.core.date.DateField;
import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.RandomUtil;
import com.slfx.easy.poi.entity.Member;
import com.slfx.easy.poi.enums.SexEnum;
import org.apache.poi.ss.usermodel.Workbook;
import org.junit.Test;

import java.io.File;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.*;

/**
 * <p>根据模板导出</p>
 *
 * @author slfx
 */
public class ExcelExportByTemplate {

    /**
     * 简单模板导出
     */
    @Test
    public void exportMemberByTemplate() {
        TemplateExportParams params = new TemplateExportParams("template/member_template.xlsx");
        Map<String, Object> map = new HashMap<String, Object>();
        List<Member> dataList = new ArrayList<>();
        for (int i = 0; i < 100; i++) {
            Member member = new Member();
            // member.setName("小明" + i);
            member.setCardNo(String.valueOf(123456789012345678L + i));
            member.setName("小明"+ (i%5 < 3 ? 1 : 2));
            member.setCompanyName("神农庄" + (i%9 < 5 ? 1 : 2));
            member.setAvatar("image/avatar" + (i % 3) + ".jpg");
            member.setBirthday(new Date());
            member.setEmail("zhangsan" + (i) + "@163.com.cn");
            member.setPhoneNumber(String.valueOf(12345678901L + i));
            member.setGender(String.valueOf(i % 2));
            member.setSex(SexEnum.getByCode(i % 2));
            member.setSalary(new BigDecimal(String.valueOf(i + 20000)).divide(new BigDecimal(3), 6, RoundingMode.HALF_UP));
            dataList.add(member);
        }
        map.put("memberList", dataList);
        Date start = new Date();
        Workbook workbook = ExcelExportUtil.exportExcel(params, map);

        // 纵向合并表格
        Map<Integer, int[]> mergeMap = new HashMap<>();
        // 第一列
        mergeMap.put(0, null);
        // 第二列，并且依赖第一列
        mergeMap.put(1, new int[]{0});
        PoiMergeCellUtil.mergeCells(workbook.getSheetAt(0), mergeMap,1);
        try {
            System.out.println("耗时：" + (new Date().getTime() - start.getTime()));
            FileOutputStream fos = new FileOutputStream("D:/poi/easypoi/exportMemberByTemplate.xlsx");
            workbook.write(fos);
            fos.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 复杂模板导出
     */
    @Test
    public void exportByTemplate() {
        TemplateExportParams params = new TemplateExportParams("template/material_acceptance_template.xlsx");
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("projectName", "xxxxxxxx");
        map.put("receiveStorage", "普通仓库1");
        map.put("orderCode", "M2022031600001");
        map.put("orderDate", DateUtil.today());
        map.put("invoiceTitle", "xxx公司");
        map.put("receiveDate", DateUtil.formatChineseDate(new Date(), false));
        map.put("receiveTime", DateUtil.format(new Date(), DatePattern.NORM_TIME_PATTERN));
        map.put("supplierTel", "1879740****");
        map.put("desc", "非常好");
        map.put("carNo", "粤xxxx");
        List<Map<String, String>> listMap = new ArrayList<Map<String, String>>();
        for (int i = 0; i < 7; i++) {
            Map<String, String> lm = new HashMap<String, String>();
            lm.put("seq", i + 1 + "");
            lm.put("materialName", "材料" + i);
            lm.put("unit", "个");
            lm.put("price", i * 10000 + "");
            lm.put("actNumber", i * 555 + "");
            lm.put("materialNumber", i * 555 + "");
            lm.put("actNumberCn", i + "大写");
            lm.put("level", "好");
            lm.put("remark", "备注" + i);

            listMap.add(lm);
        }

        map.put("materialList", listMap);
        Workbook workbook = ExcelExportUtil.exportExcel(params, map);
        File savefile = new File("D:/poi/easypoi/");
        if (!savefile.exists()) {
            savefile.mkdirs();
        }
        try {
            FileOutputStream fos = new FileOutputStream("D:/poi/easypoi/收料单.xlsx");
            workbook.write(fos);
            fos.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 横向遍历
     */
    @Test
    public void exportDynamicColumn() {
        Map<String, Object> result = new HashMap<String, Object>();
        List<DateTime> dateRangeList = DateUtil.rangeToList(DateUtil.parseDate("2022-07-01"), new Date(), DateField.DAY_OF_MONTH);
        int month = dateRangeList.get(0).getField(DateField.MONTH);
        List<Map<String, Object>> colList = new ArrayList<Map<String, Object>>();
        //先处理表头
        for (int k = 0; k < 2; k++) {
            Map<String, Object> map = new HashMap<String, Object>();
            map.put("item", (month + k) + "月考勤情况");
            for (int i = 0; i < dateRangeList.size(); i++) {
                map.put("day"  + i, (i + 1) + "日");
                map.put("work" + i, "t.hour_" + (month + k) + "_" + i);

            }
            colList.add(map);
        }

        result.put("colList", colList);


        TemplateExportParams params = new TemplateExportParams("template/attendance_template.xlsx");
        params.setColForEach(true);
        List<Map<String, Object>> valList = new ArrayList<Map<String, Object>>();
        for (int i = 0; i < 100; i++) {
            Map<String, Object> memberMap = new HashMap<String, Object>();
            BigDecimal totalHours =BigDecimal.ZERO;
            for (int k = 0; k < 2; k++) {
                memberMap.put("name", "小明" + i);
                memberMap.put("team", "智慧工地搬砖小组" + (i % 9 < 5 ? 1 : 2));

                for (int j = 0; j < dateRangeList.size(); j++) {
                    BigDecimal hour = RandomUtil.randomBigDecimal(new BigDecimal("6.5"), new BigDecimal("12.5")).setScale(2, RoundingMode.HALF_UP);
                    memberMap.put("hour_" +(month+k)+"_" + j, hour);
                    totalHours = totalHours.add(hour);
                }

            }
            memberMap.put("days", dateRangeList.size());
            memberMap.put("totalHours", totalHours);
            valList.add(memberMap);
        }
        result.put("valList", valList);
        Date start = new Date();

        Workbook workbook =ExcelExportUtil.exportExcel(params, result);
        try {
            System.out.println("耗时：" + (new Date().getTime() - start.getTime()));
            FileOutputStream fos = new FileOutputStream("D:/poi/easypoi/exportDynamicByTemplate.xlsx");
            workbook.write(fos);
            fos.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
